﻿using System.Text;
using System.Data;
using System;
using System.Collections.Generic;

namespace OF.Session
{
    public class DALBase
    {

        #region 通用分页方法
        
        /// <summary>
        /// 通用分页方法
        /// </summary>
        /// <param name="tabName">表名称或者实体名称</param>
        /// <param name="fldName">主键字段名</param>
        /// <param name="strWhere">查询条件</param>
        /// <param name="strOrder">排序条件</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页宽</param>
        /// <returns>返回查询结果，
        /// 如果页码值和页宽值不合法则返回null，
        /// 如果表名或主键字段名为空则返回null</returns>
        public DataSet GetListByPage(string tabName, string fldName, string strWhere, string strOrder, int pageIndex, int pageSize)
        {
            if (pageIndex < 1 || pageSize < 1)
                return null;

            if (string.IsNullOrWhiteSpace(tabName) || string.IsNullOrWhiteSpace(fldName))
                return null;

            int endIndex = pageIndex * pageSize;
            int startIndex = endIndex - pageSize + 1;

            var strSql = new StringBuilder();
            strSql.Append(" SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER ( ");
            if (!string.IsNullOrWhiteSpace(strOrder))
            {
                strSql.Append(" order by " + strOrder);
            }
            else
            {
                strSql.Append(" order by " + fldName + " desc ");
            }
            strSql.Append(")AS Row, T.*  from (select * from " + tabName);
            if (!string.IsNullOrWhiteSpace(strWhere))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" )T ) TT ");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1} ", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 通用分页方法
        /// </summary>
        /// <param name="tabName">表名称或者实体名称</param>
        /// <param name="fldName">主键字段名</param>
        /// <param name="strWhere">查询条件</param>
        /// <param name="strOrder">排序条件</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页宽</param>
        /// <param name="recordCount"></param>
        /// <returns>返回查询结果，
        /// 如果页码值和页宽值不合法则返回null，
        /// 如果表名或主键字段名为空则返回null</returns>
        public DataSet GetListByPage(string tabName, string fldName, string strWhere, string strOrder, int pageIndex, int pageSize, out int recordCount)
        {
            recordCount = 0;
            if (pageIndex < 1 || pageSize < 1)
                return null;

            if (string.IsNullOrWhiteSpace(tabName) || string.IsNullOrWhiteSpace(fldName))
                return null;

            int startIndex = (pageIndex - 1) * pageSize + 1;
            int endIndex = startIndex + pageSize - 1;

            var strSql = new StringBuilder();
            strSql.Append(" FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER ( ");
            if (!string.IsNullOrWhiteSpace(strOrder))
            {
                strSql.Append(" order by " + strOrder);
            }
            else
            {
                strSql.Append(" order by " + fldName + " desc ");
            }
            strSql.Append(")AS Row, T.*  from " + tabName + " T ");
            if (!string.IsNullOrWhiteSpace(strWhere))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT ");

            string sqlCount = "select count(*) " + strSql;
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(sqlCount));

            string sqlQuery = string.Format("select * {0} WHERE TT.Row between {1} and {2} ", strSql, startIndex, endIndex);
            return DbHelperSQL.Query(sqlQuery);
        }

        #endregion

    }
}